package com.saturn.sldb;

import java.sql.Connection;
import java.util.List;
import java.util.Map;

import org.json.JSONObject;

import com.saturn.app.db.DymaticCondition;
import com.saturn.app.db.ListData;
import com.saturn.app.db.ORMapping;
import com.saturn.app.db.ResultORMapping;
import com.saturn.app.db.SimpleDaoTemplate;

public class Discussion {
	private String id;
	private String identify;
	private String name;
	private String sex;
	private String home;
	
	private String audit;
	private String money;
	private String files;
	private String photo;

	private String createTime;
	private String creater;
	private String createrName;
	private String createrDepartment;
	private String state;
	private String inputType;
	private String numberNO;

	private String peopleType; // 对象类别
	private String homeNumber; //家庭编号
	private String remarks; //备注
	private String applyCause; //申请事由
	private String causeDesc; //事由描述
	private String startCause; //启动理由
	private String subject; //付款科目
	private String accountType; //户口类型
	private String registration; // 户籍属地
	private String safeWay; // 救助方式
	private String type; //类型
	private String beginTime;
	private String endTime;

	
	private static ORMapping<Discussion> mapping = new ResultORMapping<Discussion>();

		public static int add(Discussion vo) {
			// 指定值对象类型(VOClass)。例子：User
			// 指定插入表名称(tableName)。例子：如user表3个列，tableName=user(id, name, gender)
			// 根据列的顺序获取值对象的属性值。例子：vo.getId(), vo.getName(), vo.getGender()
			return SimpleDaoTemplate
					.update("INSERT INTO sldb_discussion("
							+ "identify,name,sex, home, "+ 
							"audit, money, files,  photo, createTime, creater, "+ 
							"createrName, createrDepartment,state, inputType, numberNO," +
							"peopleType, homeNumber, remarks, applyCause, causeDesc, StartCause," +
							"subject, accountType, registration, safeWay, type) VALUES(?,?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
							vo.identify, vo.name, vo.sex, vo.home, 
							vo.audit, vo.money, vo.files, vo.photo, 
							vo.createTime,vo.creater, vo.createrName,
							vo.createrDepartment,  vo.state,vo.inputType,
							vo.numberNO,vo.peopleType, vo.homeNumber,
							vo.remarks, vo.applyCause, vo.causeDesc, vo.startCause,
							vo.subject, vo.accountType,vo.registration, vo.safeWay, vo.type);
		}
		
		public static int edit(final Discussion vo) {
			return SimpleDaoTemplate.update("UPDATE sldb_discussion SET "
							+ "identify = ?, name = ?, sex = ?, home = ?, "
							+ "audit = ?, money = ?, files = ?,  photo = ?,createTime = ?, creater = ?, "
							+ "createrName = ?, createrDepartment = ?,state = ?,  inputType = ?," +
							" numberNO = ? , peopleType=?, homeNumber=?, remarks=?,applyCause=?, causeDesc=?, " +
							"startCause=?, subject=?, accountType=?, registration=?, safeWay=?, type=?"
							+ "WHERE id = ?", vo.identify, vo.name, vo.sex,vo.home,
							vo.audit, vo.money, vo.files,vo.photo, vo.createTime,
							vo.creater, vo.createrName, vo.createrDepartment, vo.state, 
							vo.inputType, vo.numberNO, vo.peopleType, vo.homeNumber, vo.remarks,
							vo.applyCause,vo.causeDesc, vo.startCause,  vo.subject, vo.accountType,
							vo.registration, vo.safeWay, vo.type, vo.id);
		}
		
		public static int editFiles(String id, String files) {
			// 指定值对象类型(VOClass)。例子：User
			// 指定插入表名称(tableName)。例子：如user表3个列，tableName=user
			// 指定修改列信息(modify)。例子：name=?, value=?
			// 根据修改列的顺序获取值对象的属性值。例子：vo.getName(), vo.getValue(), vo.getId()
			return SimpleDaoTemplate
					.update("UPDATE sldb_discussion SET "
							+ " files = ? "
							+ "WHERE id = ?",
							files, id);
		}

			public static Discussion get(String id) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定表主键(key)。例子:key=id
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.queryOne(
						"SELECT * FROM sldb_discussion WHERE 1 = 1 and id = '" + id + "'",
						null, mapping, Discussion.class);
			}
			public static Discussion getByCreater(String creater) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定表主键(key)。例子:key=id
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.queryOne(
						"SELECT * FROM sldb_discussion WHERE 1 = 1 and creater = '" + creater + "' order by id desc",
						null, mapping, Discussion.class);
			}
			
			public static List<Discussion> getAllPersonByIdcard(String identify) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定表主键(key)。例子:key=id
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return (List<Discussion>) SimpleDaoTemplate.query(
						"SELECT * FROM sldb_discussion WHERE 1 = 1 and identify = '" + identify + "'",
						null, mapping, Discussion.class);
			}

			public static Discussion getIdWithTime(String createTime) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定表主键(key)。例子:key=id
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.queryOne(
						"SELECT id FROM sldb_temporary WHERE 1 = 1 and createTime = '"+ createTime +"'",
						null, mapping, Discussion.class);
			}
			
			public static ListData<Discussion> getAll(Discussion vo, String start,
					String offset, String orderBy, String order) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.query(
						"SELECT * FROM sldb_discussion WHERE 1 = 1",
						new DymaticCondition().addSimpleCondition(vo,
								"identify", "name", "sex", "type", "createrDepartment",
								"createTime","state", "creater", "inputType", "numberNO")
								.addCondition("ORDER BY {0} {1}", orderBy,
								order), mapping, Discussion.class, start, offset);
			}
			

			public static ListData<Discussion> getAllWithOutCreater(Discussion vo, String start,
					String offset, String orderBy, String order) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.query(
						"select distinct (s.pid), t.* from  sldb_discussion t ," +
						"sldb_person_state s where 1 = 1 and t.id = s.pid",
						new DymaticCondition().addSimpleCondition(vo, "state",
								"identify", "name", "sex", "createrName",
								"createTime"), mapping, Discussion.class, start, offset);
			}
			
			public static ListData<Discussion> getAllWithoutCreater(Discussion vo, String start,
					String offset, String orderBy, String order) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.query(
						"SELECT * FROM sldb_discussion WHERE 1 = 1",
						new DymaticCondition().addSimpleCondition(vo, "state",
								"identify", "name", "gender", "createrName",
								"createTime","creater","numberNO").addCondition("ORDER BY {0} {1}", orderBy,
								order), mapping, Discussion.class, start, offset);
			}

			public static int replay(String id, String state) {
				return SimpleDaoTemplate.update(
						"UPDATE sldb_temporary SET state = ? where id = ?", state,id);
			}
			public static ListData<Discussion> getDiscussionStates(Discussion vo, String start,
					String offset, String orderBy, String order) {
				return SimpleDaoTemplate
						.query("select distinct (s.pid), p.* from  sldb_discussion p ,sldb_person_state s where 1 = 1 and p.id = s.pid",
								new DymaticCondition().addCondition("and s.userId = '?' ", vo.getCreater()), mapping,
								Discussion.class, start, offset);
			}
			
			private static int state(Connection connection, String id, String state) {
				return SimpleDaoTemplate.update(connection,
						"UPDATE sldb_temporary SET state = ? WHERE id = ?", state, id);
			}

			public static  int UpdateState(String id, String state) {
				return SimpleDaoTemplate.update(
						"UPDATE sldb_temporary SET state = ? WHERE id = ?", state, id);
			}
			
			public static int remove(final String id) {
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				return SimpleDaoTemplate.update("DELETE FROM sldb_discussion WHERE id = ?",
						id);
			}

			public static void removes(String[] ids) {
				if (ids != null) {
					for (String id : ids) {
						remove(id);
					}
				}
			}

			public static ListData<Discussion> getAllOldWithOutUserId(Discussion vo, String start,
					String offset, String orderBy, String order) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate
						.query("select distinct (s.pid), p.* from  sldb_temporary p ,sldb_temporary_state s where 1 = 1 and p.id = s.pid",
								new DymaticCondition(), mapping,
								Discussion.class, start, offset);
			}
			
			
			public static ListData<Discussion> getAllWithSelect(Discussion vo, String start,
					String offset, String orderBy, String order) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.query(
						"SELECT * FROM sldb_discussion WHERE 1 = 1",
						new DymaticCondition().addSimpleCondition(vo,
								"identify","name", "sex", "type", "createrDepartment",
								"createTime","state","money","creater")
								.addCondition("and createTime BETWEEN \'{0}\' and \'{1}\'", vo.getBeginTime(), vo.getEndTime())
								.addCondition("ORDER BY {0} {1}", orderBy,
								order), mapping, Discussion.class, start, offset);
			}
			//导出  excel  jyb
			public static List<Discussion> getAllWithSelectExcel(Discussion vo) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.query(
						"SELECT * FROM sldb_discussion WHERE 1 = 1",
						new DymaticCondition().addSimpleCondition(vo,
								"identify","name", "sex", "type", "createrDepartment",
								"createTime","state","money", "nodeType","creater")
								.addCondition("and createTime BETWEEN \'{0}\' and \'{1}\'", vo.getBeginTime(), vo.getEndTime()),
								mapping, Discussion.class);
			}
	//根据申报操作修改状态
	public static int editToState(Discussion vo) { 
		// 指定值对象类型(VOClass)。例子：User
		// 指定插入表名称(tableName)。例子：如user表3个列，tableName=user
		// 指定修改列信息(modify)。例子：name=?, value=?
		// 根据修改列的顺序获取值对象的属性值。例子：vo.getName(), vo.getValue(), vo.getId()
		return SimpleDaoTemplate
				.update("UPDATE sldb_discussion SET "
						+ "identify = ?, name = ?,sex = ?,home = ?,"
						+ "audit = ?, money = ?, files = ?,  photo = ?,createTime = ?, creater = ?, "
						+ "createrName = ?, createrDepartment = ?,state = ?,  inputType = ?," +
						" numberNO = ?, peopleType=?, homeNumber=?, remarks=?, applyCause=?, causeDesc=?," +
						"startCause=?, subject=?, accountType=?, registration=?, " +
						"safeWay=?, type=?" + "WHERE id = ?",
						vo.identify, vo.name, vo.sex, vo.home,
						vo.audit, vo.money, vo.files,vo.photo, vo.createTime,
						vo.creater, vo.createrName, vo.createrDepartment, vo.state, 
						vo.inputType, vo.numberNO, vo.peopleType, vo.homeNumber, vo.remarks,
						vo.applyCause,vo.causeDesc, vo.startCause, vo.subject, vo.accountType,
						vo.registration, vo.safeWay, vo.type, vo.id);
	}
	//更新审批人
	public static int editToAudit(Discussion vo) { 
		// 指定值对象类型(VOClass)。例子：User
		// 指定插入表名称(tableName)。例子：如user表3个列，tableName=user
		// 指定修改列信息(modify)。例子：name=?, value=?
		// 根据修改列的顺序获取值对象的属性值。例子：vo.getName(), vo.getValue(), vo.getId()
		return SimpleDaoTemplate
				.update("UPDATE sldb_discussion SET "
						+ "identify = ?, name = ?,sex = ?,home = ?,"
						+ "audit = ?, money = ?, files = ?,  photo = ?,createTime = ?, creater = ?, "
						+ "createrName = ?, createrDepartment = ?,state = ?,  inputType = ?," +
						" numberNO = ?, peopleType=?, homeNumber=?, remarks=?, applyCause=?, causeDesc=?," +
						"startCause=?, subject=?, accountType=?, registration=?, " +
						"safeWay=?, type=?" + "WHERE id = ?",
						vo.identify, vo.name, vo.sex, vo.home,
						vo.audit, vo.money, vo.files,vo.photo, vo.createTime,
						vo.creater, vo.createrName, vo.createrDepartment, vo.state, 
						vo.inputType, vo.numberNO, vo.peopleType, vo.homeNumber, vo.remarks,
						vo.applyCause,vo.causeDesc, vo.startCause, vo.subject, vo.accountType,
						vo.registration, vo.safeWay, vo.type, vo.id);
	}
	
	public Discussion(String id, String identify,  String name, String sex, 
			String home, String contact, String audit, 
			String money, String files, String createTime, String creater, String createrName, String state,
			String createrDepartment,String inputType, String numberNO, String homeNumber, String photo, String remarks,
			String accountType, String registration,String safeWay,String peopleType,String people, String month, String standard,
			String applyCause,String way, String type,
			String rescueCategory, String causeDesc, String approveType, String subject, String startCause) {
		super();
		this.id = id;
		this.identify = identify;
		this.name = name;
		this.sex = sex;
		this.home = home;
		this.audit = audit;
		this.money = money;
		this.files = files;
		this.createTime = createTime;
		this.creater = creater;
		this.createrName = createrName;
		this.state = state;
		this.createrDepartment = createrDepartment;
		this.inputType = inputType;
		this.numberNO = numberNO;
		this.homeNumber = homeNumber;
		this.photo = photo;
		this.remarks =remarks;
		this.causeDesc = causeDesc;
		this.subject = subject;
		this.startCause = startCause;
		
	}

	public Discussion() {
		super();
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getIdentify() {
		return identify;
	}

	public void setIdentify(String identify) {
		this.identify = identify;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}


	public String getHome() {
		return home;
	}

	public void setHome(String home) {
		this.home = home;
	}

	public String getAudit() {
		return audit;
	}

	public void setAudit(String audit) {
		this.audit = audit;
	}

	public String getMoney() {
		return money;
	}

	public void setMoney(String money) {
		this.money = money;
	}

	public String getFiles() {
		return files;
	}

	public void setFiles(String files) {
		this.files = files;
	}

	public String getCreateTime() {
		return createTime;
	}

	public void setCreateTime(String createTime) {
		this.createTime = createTime;
	}

	public String getCreater() {
		return creater;
	}

	public void setCreater(String creater) {
		this.creater = creater;
	}

	public String getCreaterName() {
		return createrName;
	}

	public void setCreaterName(String createrName) {
		this.createrName = createrName;
	}

	public String getState() {
		return state;
	}

	public void setState(String state) {
		this.state = state;
	}

	
	public String getCreaterDepartment() {
		return createrDepartment;
	}

	public void setCreaterDepartment(String createrDepartment) {
		this.createrDepartment = createrDepartment;
	}

	public String getInputType() {
		return inputType;
	}

	public void setInputType(String inputType) {
		this.inputType = inputType;
	}

	
	public String getPhoto() {
		return photo;
	}

	public void setPhoto(String photo) {
		this.photo = photo;
	}
	
	
	public String getNumberNO() {
		return numberNO;
	}

	public void setNumberNO(String numberNO) {
		this.numberNO = numberNO;
	}


	public String getPeopleType() {
		return peopleType;
	}

	public void setPeopleType(String peopleType) {
		this.peopleType = peopleType;
	}
	
	public String getHomeNumber() {
		return homeNumber;
	}

	public void setHomeNumber(String homeNumber) {
		this.homeNumber = homeNumber;
	}


	public String getRemarks() {
		return remarks;
	}

	public void setRemarks(String remarks) {
		this.remarks = remarks;
	}


	public String getRegistration() {
		return registration;
	}

	public void setRegistration(String registration) {
		this.registration = registration;
	}

	public String getApplyCause() {
		return applyCause;
	}

	public void setApplyCause(String applyCause) {
		this.applyCause = applyCause;
	}


	public String getCauseDesc() {
		return causeDesc;
	}

	public void setCauseDesc(String causeDesc) {
		this.causeDesc = causeDesc;
	}

	public String getStartCause() {
		return startCause;
	}

	public void setStartCause(String startCause) {
		this.startCause = startCause;
	}

	public String getSubject() {
		return subject;
	}

	public void setSubject(String subject) {
		this.subject = subject;
	}

	public String getAccountType() {
		return accountType;
	}

	public void setAccountType(String accountType) {
		this.accountType = accountType;
	}

	public String getSafeWay() {
		return safeWay;
	}

	public void setSafeWay(String safeWay) {
		this.safeWay = safeWay;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public String getBeginTime() {
		return beginTime;
	}

	public void setBeginTime(String beginTime) {
		this.beginTime = beginTime;
	}

	public String getEndTime() {
		return endTime;
	}

	public void setEndTime(String endTime) {
		this.endTime = endTime;
	}

	@Override
	public String toString() {
		return new JSONObject(this).toString();
	}
	
	public int checkSafeWay(String identity){
		String sql = "select count(*) from `sldb_country2015-08` where identify='"+identity+"' union select count(*) from `sldb_city2015-08` where identify='"+identity+"'";
		int count = SimpleDaoTemplate.queryCount(sql, null);
		return count;
	}
	
	public String checkHomeNumber(String identity){
		String sql = "select number from `sldb_country2015-08` where identify='"+identity+"' union select number from `sldb_city2015-08` where identify='"+identity+"'";
		List<Map<String,String>> resultList = SimpleDaoTemplate.query(sql, null,new String[]{"number"});
		String homeNumber = "";
		if(resultList.size()!=0){
			homeNumber = resultList.get(0).get("number");
		}
		return homeNumber;
	}
}
